classdef query < SQLstatement
  % Class for simple (single table) SQL Server queries
  % Derived from SQLstatement
  % C-tor sig: qObject = query(conn, parameters); 
  %     for details, type help('query.query')
  %
  % Author: David Goldsmith, Wash. State Dept. of Ecology, dgol461@ecy.wa.gov
  % Release date: 9/21/2011
  
    properties
      % Inherited from SQLstatement
%         con; cat; sch; meta;
%         tbls = {};
%         aCc = {}; % cell array of available columns, w/out info as to their source table(s)
%         pkey = struct('key', '', 'val', []);
%         result;
%         issuedStatementError = false;
%         issuedAvailableError = false;
%         errCode = {};
%         duration;
%     (Dependent = true)
%         DBname;
%         statement;
%         availableTables;
%         availableColumns;
        cols = {};
        orderBy = {};
        where   = '';
    end
    
    properties (SetAccess = protected)
        CSD = struct(...% CSD = Common Select "Decorators"
              'colEquivalents', {{'*', 'GETDATE()', 'GETUTCDATE()'}},...
              'globalModifiers', {{'ALL', 'DISTINCT', 'TOP \d+'}},...
              'singleColFuncs', {{'AVG', 'COUNT', 'MAX', 'MEDIAN', 'MIN', 'STDDEV', 'SUM', 'VARIANCE'}},...
              'dateFuncs', {{'DATEADD', 'DATEDIFF'}},...
              'dateParts', {{'year', 'yy', 'yyyy', 'quarter', 'qq', 'q', 'month', 'mm', 'm',...
                             'dayofyear', 'dy', 'y', 'day', 'dd', 'd', 'week', 'wk', 'ww', 'hour',...
                             'hh', 'minute', 'mi', 'n', 'second', 'ss', 's', 'millisecond', 'ms'}},...
              'as', {{'AS'}});
        allCSD = {};
    end
    
    properties (Dependent = true, Access = private)
        selectClause;
        fromClause;
        orderbyClause;
    end
    
    methods % Accessible, i.e., non-private
      % Inherited from SQLstatement
%         SQLstatement(DBname, un, pwd, devVerOverride)   
%         assembleStatement(obj) (Abstract)
%         delete(obj)             
%         procErr(obj)
%         setPropVals(obj, prop, elements, extend) (Abstract) 
%         submitStatement(obj) (Abstract) 

        function qObject = query(conn, parameters)
          % The query object's constructor
          %
          % Arguments: (req = required, opt = optional, ca = cell array)
          %
          %         conn - DBwrapper-wrapped Matlab database object, opt,
          %                if included, may be empty
          %
          %   parameters - struct, opt; the query parameters, in the following fields:
          %
          %      + cols    - ca of ca* of string(s), opt; 
          %                  the column(s) whose data one wants returned
          %
          %      + tbls    - ca of ca containing a single string, opt; 
          %                  the (single) table where these columns are to be found
          %
          %      + where   - string, opt; 
          %                  a fully-assembled, SQL Server-valid where clause
          %
          %      + orderBy - ca of ca of two-element ca(s) (ordered pairs) of strings, opt;
          %                  the column(s) to order by (first element) and the sort order 
          %                  (i.e., asc(ending) or des(cending); second element).
          %                  If more than one pair is specified, the first one gives the 
          %                  "outer-most" ordering, the second gives the next "inner-most" 
          %                  ordering, etc.
          %                  Example: {{{'SiteID','asc'}, {'PostedUTCDatetime','des'}}} returns 
          %                           results sorted alphabetically (A->Z) by SiteID, then, 
          %                           within each SiteID group, anti-chronologically 
          %                           (latest->earliest)
          %
          % *Note: the "ca of ca" syntax is required as a consequence of the way Matlab constructs 
          %        and interprets ca-valued fields in structures; an illustrative example will
          %        hopefully suffice to explain this.
          %
          %     Ex.: Suppose I want a (scalar) structure with two fields, the first to contain a cell
          %          array with one element, the second to contain a cell array of two elements,
          %          i.e., something like:
          %
          %            x = 
          %
          %                 a: {'1'}
          %                 b: {'2'  '3'}
          %
          %          One finds that the "natural" syntax:
          %
          %            >> x=struct('a', {'1'}, 'b', {'2', '3'})
          %         
          %          returns:
          %
          %            x = 
          %
          %                 1x2 struct array with fields:
          %                     a
          %                     b
          %
          %          i.e., a length-2 structure array:
          %
          %            >> x(1)
          %
          %            ans = 
          %
          %                 a: '1'
          %                 b: '2'
          %
          %            >> x(2)
          %
          %            ans = 
          %
          %                 a: '1'
          %                 b: '3'
          %
          %          with the value in the first cell array "broadcast" into field 'a' throughout
          %          the structure array!  However, the "ca of ca" syntax gets us what we want:
          %
          %            >> x=struct('a', {{'1'}}, 'b', {{'2', '3'}})
          %
          %            x = 
          %
          %                 a: {'1'}
          %                 b: {'2'  '3'}
          %
          %          Thus the ca-of-ca syntax is required so that we may have cell arrays of
          %          different sizes in the different fields of the structure

            if nargin < 1, conn = ''; end
            if nargin < 2, parameters = struct.empty; end
            
            qObject = qObject@SQLstatement(conn);
            for f = fields(qObject.CSD)'
                qObject.allCSD = union(qObject.allCSD, qObject.CSD.(f{:}));
            end

            paraFields = fieldnames(parameters);
            for prop={'tbls', 'cols', 'orderBy', 'where'}
                prop = prop{:};
                if ismember(prop, paraFields)
                    qObject.setPropVals(prop, parameters.(prop));
                end
            end
            
        end
        
        function validateInput(obj, prop, elements, extend)
            
            global lfcr
            
            if isempty(lfcr)
                lfcr = char(10);
            end
            
            switch prop
                
                case 'where'
                    if ~(isempty(elements) || ischar(elements) || iscellstr(elements))
                        obj.errCode{end+1} = 'query.setPropVals error: wrong type input for where';
                    end
                    
                case 'tbls'
                    if numel(elements)~=1
                        obj.errCode{end+1} = 'query.setPropVals error: received more than one value for tbls';
                    elseif numel(elements)==1 && extend
                        obj.errCode{end+1} = ['query.setPropVals error: asked to make tbls contain more than '...
                                       'one element; this is not allowed in a "non-join" query, please '...
                                       'use the injoinQuery class'];
                    elseif ~iscellstr(elements)
                        obj.errCode{end+1} = 'query.setPropVals error: wrong type for tbls';
                    elseif ~any(ismember(elements{1}, obj.availableTables))
                        obj.errCode{end+1} = ['query.setPropVals error: invalid table ' elements{1}];
                    end
                    
                case 'orderBy'
                    if any(~(cellfun(@iscellstr, elements) & cellfun(@numel, elements)==2)) 
                        obj.errCode{end+1} = ['query.setPropVals(orderBy) error: at least one input value '...
                                       'for the "orderBy" property' lfcr 'is not a length-2 cell '...
                                       'array of strings'];
                    else
                        elements = [elements{:}];
                        orderSpecs = elements(2*(1:(numel(elements)/2))); 
                        if any(~ismember(orderSpecs, {'asc', 'des'}))
                            obj.errCode{end+1} = ['query.setPropVals(orderBy) error: at least one '...
                                                  'order specifier is neither ''asc''' lfcr...
                                                  'nor ''des''.  (Sorry, case must match.)'];
                        end
                        obj.confirmColAvail(elements(2*(1:(numel(elements)/2))-1), prop);                        
                    end

                case 'cols'
                    if ~(isempty(elements) || iscellstr(elements))
                        obj.errCode{end+1} = 'query.setPropVals error: wrong type input for cols';
                    else
                        obj.confirmColAvail(elements, prop);
                    end
                    
                otherwise
                    obj.errCode{end+1} = ['query.setPropVals error: invalid prop ' prop];
                    
            end
            
        end

        function confirmColAvail(obj, cols, prop)

            global lfcr
            
            if isempty(obj.tbls)
                obj.errCode{end+1} = ['query.setPropVals(' prop ') error: tbls property unset, can''t '...
                               'assure availability of any columns, so not setting property ' prop];
            else           
                obj.availableColumns; % this call makes sure obj.aCc is up-to-date
                switch prop
                    
                    case 'orderBy'
                        if ~isempty(setdiff(cols, obj.aCc))
                            obj.errCode{end+1} = ['query.setPropVals(orderBy) error: given the '...
                               'current table list,' lfcr 'at least one column requested for the '...
                               'ORDER BY clause is not presently available' lfcr '(or may not '...
                               'exist at all in the current DB).'];
                        end
                        
                    case 'cols'
                      % Note: this check doesn't guarantee SELECT clause syntactical correctness,
                      % but it is intended to get pretty close
                        
                        nc = numel(cols);
                        cEs = union(obj.aCc, obj.CSD.colEquivalents); % column "equivalents" includes
                                                                      % columns, of course

                        gMs = cellstrIsMember(cols, obj.CSD.globalModifiers);
                        allDisPos = cellstrIsMember(cols, {'ALL', 'DISTINCT'});
                        topPos = gMs - allDisPos;
                        if gMs(end) || (sum(abs(diff(gMs))) > 1)
                            obj.errCode{end+1} = ['query.setPropVals(cols) syntax error: one or more '...
                                                  '"global modifiers," ' lfcr sprintf('%s, ',... 
                                                  obj.CSD.globalModifiers{1:end-1})...
                                                  obj.CSD.globalModifiers{end}(1:end-3) '<integer> '...
                                                  'follows something other than a global modifier, '...
                                                  lfcr 'or you''ve provided only global modifiers '...
                                                  'for the cols property.'];
                        elseif find(topPos, 1) < find(allDisPos, 1)
                            obj.errCode{end+1} = ['query.setPropVals(cols) syntax error: a "TOP" in '...
                                                  'your requested SELECT clause' lfcr 'precedes '...
                                                  'an "ALL" or "DISTINCT"; SQL Server considers this '...
                                                  'a syntax error.'];
                        end
                        
                        sMs = find(cellstrIsMember(cols, obj.CSD.singleColFuncs));
                        if ismember(nc, sMs) || ... % last element can't be a single column function
                           any(~cellstrIsMember(cols(sMs+1), cEs)) % a col. equiv. must follow a s.c.f.
                            obj.errCode{end+1} = ['query.setPropVals(cols) syntax error: one or more '...
                                                  '"single-column functions," ' lfcr sprintf('%s, ',... 
                                                  obj.CSD.singleColFuncs{:}) lfcr...
                                                  'present in your requested SELECT clause is not '...
                                                  'immediately followed by an available' lfcr...
                                                  'column or "column equivalent" (' sprintf('%s, ',...
                                                  obj.CSD.colEquivalents{1:end-1})...
                                                  obj.CSD.colEquivalents{end} ').'];
                        end
                        
                        mMs = find(cellstrIsMember(cols, obj.CSD.dateFuncs));
                        prefix = 'query.setPropVals(cols) syntax error: ';
                        if ~isempty(mMs) && nc > 3
                            if any(ismember(nc-2:nc, mMs))
                                obj.errCode{end+1} = [prefix 'the datetime functions, ' sprintf('%s, ',...
                                                      obj.CSD.dateFuncs{:}) lfcr 'may not be '...
                                                      'among the last three elements in a SELECT clause.']; 
                            else

                                if any(~cellstrIsMember(cols(mMs+1), obj.CSD.dateParts))
                                    obj.errCode{end+1} = [prefix 'the datetime functions, ' sprintf('%s, ', ...
                                                          obj.CSD.dateFuncs{:}) lfcr 'must be '...
                                                          'immediately followed by one of '...
                                                          sprintf('%s, ', obj.CSD.dateParts{1:12}) lfcr...
                                                          sprintf('%s, ', obj.CSD.dateParts{13:end-1})...
                                                          sprintf('%s.', obj.CSD.dateParts{end})]; 
                                end

                                for indx = mMs
                                    func = cols{indx};
                                    scndArg = cols{indx+2};
                                    isScndArgCol = ismember(scndArg, obj.aCc); % exclude col equivalents
                                    tmp = extract_leading_number(scndArg);
                                    isNum = isnumeric(tmp) && ~isempty(tmp);
                                    switch func
                                        case 'DATEADD' % second argument must be an int type
                                            if isScndArgCol
                                                scndArgTyp = getColumnType(scndArg, obj.tbls{:}, obj.con);
                                                if ~iscell(scndArgTyp)
                                                    scndArgTyp = {scndArgTyp};
                                                end
                                                if all(cellfun(@isempty, regexpi(scndArgTyp, 'int')))
                                                    obj.errCode{end+1} = [prefix 'Second argument of DATEADD '...
                                                        scndArg ' not the required (i.e., integer) type.'];
                                                end
                                                for sAT=scndArgTyp'
                                                    sAT = sAT{:};
                                                    if any(~cellfun(@isempty, regexp(sAT, ...
                                                           {'Attempt', 'Query', 'Sorry'})))
                                                        obj.errCode{end+1} = sAT;
                                                        break
                                                    end
                                                end
                                            elseif isNum
                                                nScndArg = str2double(scndArg);
                                                if numel(int64(nScndArg)) > 1 || int64(nScndArg) ~= nScndArg
                                                    obj.errCode{end+1} = [prefix 'Second argument of DATEADD '...
                                                        scndArg ' not the required (i.e., integer) type.'];
                                                end
                                            end
                                            
                                        case 'DATEDIFF' % second argument must be a datetime type
                                            if isScndArgCol
                                                scndArgTyp = getColumnType(scndArg, obj.tbls{:}, obj.con);
                                                if ~iscell(scndArgTyp)
                                                    scndArgTyp = {scndArgTyp};
                                                end
                                                for sAT=scndArgTyp'
                                                    sAT = sAT{:};
                                                    if all(cellfun(@isempty, regexpi(sAT, ...
                                                            {'date', 'time'})))
                                                        obj.errCode{end+1} = [prefix 'Second argument of DATEDIFF '...
                                                            scndArg ' not the required (i.e., datetime) type.'];
                                                        break
                                                    elseif any(~cellfun(@isempty, regexp(sAT, ...
                                                                {'Attempt', 'Query', 'Sorry'})))
                                                        obj.errCode{end+1} = sAT;
                                                        break
                                                    end
                                                end
                                            else
                                                if ~isDateTime(scndArg)
                                                    obj.errCode{end+1} = [prefix 'Couldn''t convert second '...
                                                        'argument of DATEDIFF ' scndArg ' to the required '...
                                                        '(i.e., datetime) type.'];
                                                end
                                            end
                                            
                                    end
                                end

                                thrdArg = cols(indx+3);
                                for col = thrdArg'
                                    col = col{:};
                                    if ismember(col, obj.aCc) % if col is an available column in the table
                                        thrdArgTyp = getColumnType(col, obj.tbls{:}, obj.con);
                                        if any(~cellfun(@isempty, regexp(thrdArgTyp, {'Attempt', ...
                                                'Query', 'Sorry'})))
                                            obj.errCode{end+1} = thrdArgTyp;
                                        elseif all(cellfun(@isempty, regexpi(thrdArgTyp, {'date', 'time'})))
                                            obj.errCode{end+1} = [prefix 'the datetime functions, '...
                                                                  sprintf('%s, ', obj.CSD.dateFuncs{:})...
                                                                  lfcr 'require a datetime type for '...
                                                                  'their third argument.'];
                                        end
                                    elseif ~isDateTime(col)
                                        obj.errCode{end+1} = [prefix 'Couldn''t convert third argument of '...
                                            'datetime function ' cols{indx} lfcr 'to the required '...
                                            '(i.e., datetime) type.'];
                                    end
                                end
                            end
                            
                        elseif ~isempty(mMs) % && nc <= 3 implies a datetime function was found, 
                                             % but there are fewer than the required three arguments
                            obj.errCode{end+1} = [prefix 'Not enough arguments for your datetime '...
                                                  'function(s).  (Exactly three are required.)'];
                        end
                        
                        as = find(cellstrIsMember(cols, obj.CSD.as));
                        if ~isempty(as)
                            if any([1 nc]==as) % AS can't be first or last
                                obj.errCode{end+1} = ['query.setPropVals(cols) syntax error: AS can be '...
                                                      'neither the first nor the last element' lfcr...
                                                      'in a SELECT clause.'];
                            elseif any(~cellstrIsMember(cols(as-1), cEs)) % AS must be preceded by a c.e.
                              % AS must also be followed by a string literal, but this is already garanteed
                                obj.errCode{end+1} = ['query.setPropVals(cols) syntax error: AS must be '...
                                                      'immediately preceded by a' lfcr '"column '...
                                                      'equivalent."'];
                            end
                        end
                        
                end
            end
        end

        function setPropVals(obj, prop, elements, extend)
          % Sets property values, enforcing content restrictions => use instead of direct assignment
          % extend is an optional logical indicating whether to replace (false, the default) or 
          % supplement (true) obj.(prop) with elements
            if nargin < 4
                extend = false;
            end

            if ~(isequal(prop, 'where') || iscell(elements))
                elements = {elements};
            end
            
          % Refresh error code container
            obj.errCode = {};
            
          % Validate inputs
            obj.validateInput(prop, elements, extend);
            
            if isempty(obj.errCode)
              % If changing the table, save the current list of valid columns
              % for later use checking the where clause
                if strcmp(prop, 'tbls') && ~isempty(obj.tbls)
                    obj.availableColumns; % update obj.aCc
                    oldValidCols = obj.aCc;
                end
                
              % The "Action"
                if extend 
                    obj.(prop) = extendArray(obj.(prop), elements);
                else
                    obj.(prop) = elements;
                end
                
                if exist('oldValidCols', 'var')
                    obj.processTblsChange(oldValidCols);
                end
                
            end

        end
        
        function processTblsChange(obj, oldValidCols)
          % If changing the table: cols, orderBy, and where may need modification (and, in general,
          % where can't be "corrected" automatically, so will just be emptied if necessary)
            obj.availableColumns; % make sure obj.aCc is up-to-date
          % Keep those cols which are still available
            tmpc = {};
            for i=1:numel(obj.cols)
                if ismember(obj.cols{i}, obj.aCc) || ~isempty(strfind(obj.cols{i}, '*'))
                    tmpc{end+1} = obj.cols{i};
                end
            end
            if ~isequal(obj.cols, tmpc)
                obj.cols = tmpc;
                obj.errCode{end+1} = ['query.setPropVals ALERT: table change required change in '...
                               'cols property'];
            end
          % Do the same for orderBy
            tmpoB = {};
            for i=1:numel(obj.orderBy)
                if ismember(obj.orderBy{i}{1}, obj.aCc) 
                    tmpoB{end+1} = obj.orderBy{i};
                end
            end
            if ~isequal(flatten(obj.orderBy), flatten(tmpoB))
                obj.orderBy = tmpoB;
                obj.errCode{end+1} = ['query.setPropVals ALERT: table change required change in '...
                               'orderBy property'];
            end
          % Clear where if necessary
            if exist('oldValidCols', 'var')
                orphanedCols = setdiff(oldValidCols, obj.aCc);
                for c = orphanedCols
                    if ~isempty(strfind(obj.where, c{:}))
                        obj.where = '';
                        obj.errCode{end+1} = ['query.setPropVals ALERT: table change required '...
                                       'emptying of where property'];
                        break;
                    end
                end
            end
        end
        
        function sC = get.selectClause(obj)
            sC = 'SELECT ';
            nc = numel(obj.cols);
            i = 1;
            while i < nc + 1

                word = obj.cols{i};

                if any(~cellfun(@isempty, regexp(word, obj.CSD.globalModifiers, 'once')))
                    sC = [sC word ' '];
                    i = i + 1;
                        
                elseif (i < nc) && any(~cellfun(@isempty, regexp(word, obj.CSD.singleColFuncs, 'once')))
                    sC = [sC word '(' obj.cols{i+1} '), '];
                    i = i + 2;

                elseif (i < nc - 2) && any(~cellfun(@isempty, regexp(word, obj.CSD.dateFuncs, 'once')))
                    sC = [sC word '(' obj.cols{i+1} ',' obj.cols{i+2} ',' obj.cols{i+3} '), '];
                    i = i + 4;

                elseif (i < nc - 1) && strcmpi(obj.cols{i+1}, 'AS')
                    sC = [sC word ' AS ' obj.cols{i+2} ', '];                        
                    i = i + 3;
                    
                else
                    sC = [sC word ', '];                        
                    i = i + 1;
                    
                end
                
            end
            
            if strcmp(sC(end-1), ',')
                sC = [sC(1:end-2) sC(end)];
            end
            
        end
                
        function fC = get.fromClause(obj)
            fC = ['FROM ' obj.tbls{:}];
        end
                
        function oC = get.orderbyClause(obj)
            oC = 'ORDER BY ';
            if numel(obj.orderBy) > 1
                tmp = flatten(obj.orderBy(1:end-1));
                oC = [oC sprintf('%s %s, ', tmp{:})];
            end
            oC = [oC sprintf('%s %s;', obj.orderBy{end}{:})];
            oC = regexprep(oC, {'asc', 'des'}, {'ASC', 'DESC'}, 'ignorecase');
        end
                
        function S = assembleStatement(obj)
            S = [obj.selectClause ' ' obj.fromClause];
            if ~isempty(obj.where)
                S = [S ' WHERE ('];
                for i=1:(numel(obj.where)-1)
                    S = [S obj.where{i} ') OR ('];
                end
                S = [S obj.where{end} ')'];
            end
            if ~isempty(obj.orderBy)
                S = [S ' ' obj.orderbyClause];
            else
                S = [S ';'];
            end
        end
        
        function submitStatement(obj)
            tic;
                curs = exec(obj.con, obj.statement);
            obj.duration = toc;
            if isempty(curs.Message)
                curs = fetch(curs);
                if isempty(curs.Message)
                    obj.result = curs.Data;
                else
                    obj.errCode{end+1} = curs.Message;
                end
                if strcmp(obj.result, 'No Data')
                    obj.result = {};
                    obj.errCode{end+1} = 'Query returned no data';
                end
            else
                obj.errCode{end+1} = curs.Message;
            end
        end
        
        function S = structFromResult(obj)
          % Creates a struct from obj.result using obj.cols as the field names
            if isempty(obj.cols) || isempty(obj.result)
                S = ['query.structFromResult error: properties cols and/or result '...
                                      'are empty'];
            elseif numel(obj.cols) ~= size(obj.result, 2)
                S = ['query.structFromResult error: number of column labels doesn''t '...
                                      'equal the number of columns returned by the query'];
            else
                for i=1:numel(obj.cols)
                    S.(obj.cols{i}) = obj.result(:,i);
                end
            end
        end
        
    end
    
    methods (Access = protected)
        
        function la = isCSD(obj, arg) % returns 1 where arg is a Common Select Decorator, 
                                      % 0 where it isn't
            la = ismember(arg, obj.CSD);
        end
        
    end
    
end